/*
  Script de Actualizacion 001
  Se Agrega la funcionalidad de CxC , CxP , la busqueda de entidades x rol (Clientes, Proveedores)
*/

--USE MEDATA
USE MEDATA

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T013]') AND name = N'PK__T013__2A4B4B5E')
ALTER TABLE [dbo].[T013] DROP CONSTRAINT [PK__T013__2A4B4B5E]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T013__T011NoClie__2B3F6F97]') AND parent_object_id = OBJECT_ID(N'[dbo].[T013]'))
ALTER TABLE [dbo].[T013] DROP CONSTRAINT [FK__T013__T011NoClie__2B3F6F97]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T013__T003Rol__2C3393D0]') AND parent_object_id = OBJECT_ID(N'[dbo].[T013]'))
ALTER TABLE [dbo].[T013] DROP CONSTRAINT [FK__T013__T003Rol__2C3393D0]
GO

ALTER TABLE [dbo].[T013] DROP COLUMN  [T013IdRol]
GO
---
--- CREATE COLUMN: T013IdRol
---
ALTER TABLE [dbo].[T013] ADD [T013IdRol] int NOT NULL IDENTITY
GO
---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T013] ADD 
	FOREIGN KEY ([T011NoCliente])
		REFERENCES [dbo].[T011]([T011NoCliente])
GO
---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T013] ADD 
	FOREIGN KEY ([T003Rol])
		REFERENCES [dbo].[T003]([T003Rol])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


-- Actualizar lista de roles

INSERT INTO T003 (T003Rol,T003Descripcion) VALUES ('PROVEEDOR','Proveedor')
INSERT INTO T003 (T003Rol,T003Descripcion) VALUES ('CLIENTE','Cliente')


---
--- CREATE TABLE: "T024" Proveedores
---
CREATE TABLE [T024]
(
	[T024IdProveedor] int NOT NULL,
	[T024Nombre] varchar(360) NOT NULL,
	[T024Rfc] varchar(16),
	[T024Domicilio] varchar(400),
	PRIMARY KEY CLUSTERED ([T024IdProveedor])
)
GO
---
--- CREATE TABLE: "T025" Facturas CxC
---
---
--- CREATE TABLE: "dbo"."T025"
---
CREATE TABLE [dbo].[T025]
(
	[T025IdFactura] int NOT NULL IDENTITY,
	[T011NoCliente] int,
	[T025NoFactura] varchar(32) NOT NULL,
	[T025Importe] decimal(18,6) NOT NULL,
	[T025FechaIngreso] datetime NOT NULL,
	PRIMARY KEY CLUSTERED ([T025IdFactura])
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T025]
(
	[T011NoCliente]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T025] ADD 
	FOREIGN KEY ([T011NoCliente])
		REFERENCES [dbo].[T011]([T011NoCliente])
GO

---
--- CREATE TABLE: "T026" Facturas CxP
---
---
CREATE TABLE [T026]
(
	[T026IdFactura] varchar(20) NOT NULL,
	[T011NoCliente] int,
	[T026NoFactura] varchar(32) NOT NULL,
	[T026Importe] decimal(18,6) NOT NULL,
	[T026FechaIngreso] datetime NOT NULL,
	PRIMARY KEY CLUSTERED ([T026IdFactura])
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [T026]
(
	[T011NoCliente]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T026] ADD 
	FOREIGN KEY ([T011NoCliente])
		REFERENCES [dbo].[T011]([T011NoCliente])
GO

---
--- CREATE PROCEDURE: GetEntidadesByRol
---
CREATE PROCEDURE [dbo].[GetEntidadesByRol]
@Rol as varchar(10) 
AS
SET NOCOUNT ON;
	select a.T011NoCliente,a.T011Nombre,a.T011Domicilio,a.T011RFC,T003Rol
	from T011 a 
	inner join T013 b on a.T011NoCliente=b.T011NoCliente
	where b.T003Rol=@Rol
	order by a.T011NoCliente

GO

---
--- Modificacion de Vista para que incluya la Carta Porte del Cliente
---
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[FacturasView]
AS
SELECT     dbo.T005.T005FechaInicio, dbo.T005.T006NoUnidad, dbo.T021.T021NoCartaPorte,dbo.T021.T021NoCartaPorteCte, dbo.T011.T011Nombre, dbo.T005.T007IdRemolque, 
                    dbo.T008.T008Nombre AS Origen,T008_1.T008Nombre as Destino, dbo.T021.T021Subtotal
FROM         dbo.T005 
					  INNER JOIN dbo.T021 ON dbo.T005.T005NoViaje = dbo.T021.T005NoViaje 
                      INNER JOIN dbo.T011 ON dbo.T005.T011NoCliente = dbo.T011.T011NoCliente 
                      INNER JOIN dbo.T008 ON dbo.T005.T008IdOrigen = dbo.T008.T008IdOrigen 
                      LEFT JOIN dbo.T008 AS T008_1 ON dbo.T005.T008IdDestino = T008_1.T008IdOrigen
Where dbo.T021.T021Facturada='N'
GO



DECLARE @T027IdScript as varchar(80)
DECLARE @T027Descripcion as varchar(360)
SET @T027IdScript='CS-001-09'
SET @T027Descripcion='Tabla de Roles'
INSERT INTO T027(T027IdScript,T027Descripcion,T027FechaRealizado) VALUES (@T027IdScript,@T027Descripcion,getdate())